﻿	CREATE DATABASE QLMN

	USE QLMN
	set dateformat dmy

	CREATE TABLE HOCSINH
	(
		MaHS int identity(11520001, 1),
		TenHS nvarchar(50),
		Ngaysinh smalldatetime,
		Gioitinh varchar(10),
		MaPH int,
		CONSTRAINT PK_HS PRIMARY KEY (MaHS)
	)
	drop table HOCSINH
	set dateformat dmy
	ALTER TABLE HOCSINH ADD CONSTRAINT HOCSINH_Ngaysinh CHECK ((YEAR(GETDATE())-YEAR(Ngaysinh))>2 AND (YEAR(GETDATE())-YEAR(Ngaysinh))<7)
	ALTER TABLE HOCSiNH ADD CONSTRAINT HOCSINH_Gioitinh CHECK (Gioitinh IN ('Nam', 'Nu')) 



	ALTER TABLE HOCSINH ADD CONSTRAINT FK_HOCSINH FOREIGN KEY (MaPH) REFERENCES PHUHUYNH(MaPH)


	drop table HOCSINH
	INSERT INTO HOCSINH(TenHS, Ngaysinh, Gioitinh, MaPH) VALUES (N'Nguyễn Thúy Hà','01/02/2011','Nu', 15520001)
	INSERT INTO HOCSINH(TenHS, Ngaysinh, Gioitinh, MaPH) VALUES (N'Trần Thị Kim Chung','02/12/2010','Nam', 15520002)

	create proc DS_HocSinh

	as
	begin
		Select *from HOCSINH
	end

	CREATE PROCEDURE DS_HocSinhTheoMa
		@MaHS int
	AS
		SELECT * FROM HOCSINH WHERE MaHS = @MaHS
	
	CREATE PROCEDURE DS_HocSinhTheoTen
		@TenHS nvarchar(50)
	AS
		SELECT * FROM HOCSINH WHERE TenHS LIKE '%'+ @TenHS +'%'

	CREATE PROCEDURE DS_HocSinhTheoLop
		@Malop varchar(10)
	AS
		SELECT  HOCSINH.MaHS, TenHS, Ngaysinh, Gioitinh, MaPH
		FROM HOCSINH, DANHSACHLOP
		WHERE HOCSINH.MaHS = DANHSACHLOP.MaHS AND
				Malop=@Malop


	----------------------------------------------------------------------------
	CREATE TABLE NHANVIEN
	(
		MaNV int identity(12520001, 1),
		Malop varchar(10),
		TenNV nvarchar(50),
		LoaiNV nvarchar(50),
		MatKhau VARCHAR(20) not null
		CONSTRAINT PK_NV PRIMARY KEY (MaNV)
	)
	alter table NhanVien add constraint DF_MatKhau default '575239ABC' for MatKhau
	ALTER TABLE NHANVIEN ADD CONSTRAINT FK_NHANVIEN FOREIGN KEY(Malop) REFERENCES LOP(Malop)

	INSERT INTO NHANVIEN (Malop, TenNV, LoaiNV) VALUES ('M1', N'Trần Minh Hà',N'Nhân viên y tế')
	INSERT INTO NHANVIEN (Malop, TenNV, LoaiNV) VALUES ('NKAV1',N'Thái Bảo Trân',N'Giáo viên')
	INSERT INTO NHANVIEN (Malop, TenNV, LoaiNV) VALUES ('C2',N'Lê Diệu Thúy',N'Bảo mẫu')
	INSERT INTO NHANVIEN (Malop, TenNV, LoaiNV) VALUES ('C3',N'Trịnh Cát Tường',N'Giáo viên')


	create proc ThemNV
	@Malop varchar(10),
	@TenNV varchar(50),
	@LoaiNV nvarchar(50)

	as
	begin
		insert into NHANVIEN(Malop, TenNV, LoaiNV) values (@Malop, @TenNV, @LoaiNV)
	end
	go

	CREATE PROC DS_NHANVIENYTE
	AS
	BEGIN
		SELECT *FROM NHANVIEN
		WHERE LoaiNV=N'Nhân viên y tế'
	END
	drop proc ds_nhanvienvanphong
	CREATE PROC DS_NHANVIENVANPHONG
	AS
	BEGIN
		SELECT *FROM NHANVIEN
		WHERE LoaiNV=N'Nhân viên văn phòng'
	END

	drop proc DS_NHANVIENYTE
	INSERT INTO NHANVIEN(Malop, TenNV, LoaiNV) VALUES ('M1', N'Trần Minh Hà',N'Nhân viên hỗ trợ vệ sinh')
	INSERT INTO NHANVIEN(Malop, TenNV, LoaiNV) VALUES ('C2',N'Hồ Thị Lan',N'Giáo viên')
	INSERT INTO NHANVIEN(Malop, TenNV, LoaiNV) VALUES ('L1',N'Lê Bảo Hạnh',N'Nhân viên y tế')
	INSERT INTO NHANVIEN(Malop, TenNV, LoaiNV) VALUES ('L2',N'Ngô Tuấn Trần',N'Nhân viên y tế')
	INSERT INTO NHANVIEN(Malop, TenNV, LoaiNV) VALUES ('C3',N'Ngô Tuấn Hưng',N'Nhân viên văn phòng')


	----------------------------------------------------------------------------------------
	CREATE TABLE LOP
	(
		Malop varchar(10),
		MaDV varchar(10),
		Tenlop nvarchar(50),
		Siso int,
		CONSTRAINT PK_LOP PRIMARY KEY (Malop)
	)
	ALTER TABLE LOP ADD CONSTRAINT FK_LOP FOREIGN KEY(MaDV) REFERENCES DICHVU(MaDV)


	INSERT INTO LOP VALUES ('M1',NULL,N'Mầm 1',0)
	INSERT INTO LOP VALUES ('M2',NULL,N'Mầm 2',0)
	INSERT INTO LOP VALUES ('M3',NULL,N'Mầm 3',0)
	INSERT INTO LOP VALUES ('L1',NULL,N'Lá 1',0)
	INSERT INTO LOP VALUES ('L2',NULL,N'Lá 2',0)
	INSERT INTO LOP VALUES ('NKAV1','NKAV',N'Năng khiếu anh văn 1',0)
	INSERT INTO LOP VALUES ('NKVE1','NKVE',N'Năng khiếu vẽ 1',0)
	INSERT INTO LOP VALUES ('NKVE2','NKVE',N'Năng khiếu vẽ 2',0)
	INSERT INTO LOP VALUES ('GTNG1','GTNG',N'Giữ trẻ ngoài giờ 1',0)
	INSERT INTO LOP VALUES ('NKTD1','NKTD',N'Năng khiếu thể dục 1',0)
	INSERT INTO LOP VALUES ('C1',NULL,N'Chồi 1',0)
	INSERT INTO LOP VALUES ('C2',NULL,N'Chồi 2',0)
	INSERT INTO LOP VALUES ('C3',NULL,N'Chồi 3',0)

	create proc DS_Lop

	as
	begin
		Select *from LOP
	end
	go



	create proc DS_LopThuong
	as
	begin
		select *from LOP
		where MaDV is null
	end

	-----------------------------------------------------------------------
	CREATE TABLE PHUHUYNH
	(
	MaPH int identity(15520001, 1),
	TenPH nvarchar(50),
	Nghenghiep nvarchar(50),
	Diachi nvarchar(50),
	CMND varchar(15),
	SDT varchar(15),
	CONSTRAINT PK_PH PRIMARY KEY (MaPH)
	)

	INSERT INTO PHUHUYNH(TenPH, Nghenghiep, Diachi, CMND, SDT) VALUES (N'Nguyễn Thùy Trâm', N'Giáo viên', N'19 Mạc Đỉnh Chi, Bình Tân, TP.HCM', '212474532', '01675551875')
	INSERT INTO PHUHUYNH(TenPH, Nghenghiep, Diachi, CMND, SDT) VALUES (N'Trần Minh Thu', N'Công nhân', N'Quận 8, TP.HCM', '212474532', '01675551875')

	create proc DS_PhuHuynh

	as
	begin
		Select *from PHUHUYNH
	end
	go

	----------------------------------------------------------------------------

	CREATE TABLE DANHSACHLOP
	(
		Malop varchar(10),
		MaHS int,
		CONSTRAINT PK_DSL PRIMARY KEY (Malop,MaHS)
	)

	ALTER TABLE DANHSACHLOP ADD CONSTRAINT FK1_DANHSACHLOP FOREIGN KEY (Malop) REFERENCES LOP(Malop)
	ALTER TABLE DANHSACHLOP ADD CONSTRAINT FK2_DANHSACHLOP FOREIGN KEY (MaHS) REFERENCES HOCSINH(MaHS)


	CREATE PROCEDURE DS_DanhSachLop
	
	AS
		Select *from DANHSACHLOP



	----------------------------------------------------------------------------

	CREATE TABLE PHIEUKHAM
	(
		SoPK int identity(1, 1),
		MaHS int,
		MaNV int,
		Lankham int,
		Ngaykham smalldatetime,
		Noidungkham nvarchar(200),
		Ketluan nvarchar(200),
		CONSTRAINT PK_PK PRIMARY KEY (SoPK)
	)

	ALTER TABLE PHIEUKHAM ADD CONSTRAINT FK1_PHIEUKHAM FOREIGN KEY(MaHS) REFERENCES HOCSINH(MaHS)
	ALTER TABLE PHIEUKHAM ADD CONSTRAINT FK2_PHIEUKHAM FOREIGN KEY(MaNV) REFERENCES NHANVIEN(MaNV)

	create proc ThemPhieuKham
	@MaHS int,
	@MaNV int,
	@Lankham int,
	@Ngaykham smalldatetime,
	@Noidungkham nvarchar(200),
	@Ketluan nvarchar(200)
	as
	begin
		insert into PHIEUKHAM(MaHS, MaNV, Lankham, Ngaykham, Noidungkham, Ketluan) values (@MaHS, @MaNV, @Lankham, @Ngaykham, @Noidungkham, @Ketluan)
	end
	go

	create proc DS_Phieu
	@MaHS int
	as
	begin
		Select *from PHIEUKHAM WHERE MaHS=@MaHS
	end
	create proc DS_PhieuKham
	as
	begin
		Select *from PHIEUKHAM
	end
	go

	create PROCEDURE DS_PhieuKhamTheoMaHS
	@MaHS int
	as
	begin
		Select *from PHIEUKHAM
		where MaHS=@MaHS
	end

	----------------------------------------------------------

	CREATE TABLE THUCDON
	(
	MaTD int identity (1,1),
	Thu varchar(10),
	Ngay smalldatetime,
	Ansang nvarchar(50),
	Angiuagio nvarchar(100),
	Antrua nvarchar(200),
	Anxe nvarchar(50),
	CONSTRAINT PK_TD PRIMARY KEY (MaTD)
	)


	CREATE PROCEDURE DS_ThucDonTheoThu
		@Thu varchar(10)
	AS
		SELECT * FROM THUCDON WHERE Thu = @Thu



	CREATE PROCEDURE DS_ThucDonTheoNgay
		@Ngay smalldatetime
	AS
		SELECT * FROM THUCDON WHERE Ngay = @Ngay

	CREATE PROCEDURE DS_ThucDon
	
	AS
		SELECT * FROM THUCDON
	-----------------------------------------------------

	CREATE TABLE THUCDONLOP
	(
	MaTD int,
	Malop varchar(10),
	CONSTRAINT PK_TDl PRIMARY KEY (MaTD,Malop)
	)

	ALTER TABLE THUCDONLOP ADD CONSTRAINT FK1_THUCDONLOP FOREIGN KEY(MaTD) REFERENCES THUCDON(MaTD)
	ALTER TABLE THUCDONLOP ADD CONSTRAINT FK2_THUCDONLOP FOREIGN KEY(Malop) REFERENCES LOP(Malop)

	--------------------------------------------------

	CREATE TABLE LICHHOC
	(
	MaLH int identity(1,1),
	Malop varchar(10),
	Tuan int,
	Thang int,
	Thu2 nvarchar(100),
	Thu3 nvarchar(100),
	Thu4 nvarchar(100),
	Thu5 nvarchar(100),
	Thu6 nvarchar(100),
	CONSTRAINT PK_LH PRIMARY KEY (MaLH)
	)
	ALTER TABLE LICHHOC ADD CONSTRAINT FK_LICHHOC FOREIGN KEY (Malop) REFERENCES LOP(Malop)

	CREATE PROCEDURE DS_LichHocTheoTuan
		@Tuan int
	AS
		SELECT * FROM LICHHOC WHERE Tuan = @Tuan

	CREATE PROCEDURE DS_LichHocTheoThang
		@Thang int
	AS
		SELECT * FROM LICHHOC WHERE Thang = @Thang

	CREATE PROCEDURE DS_LichHocTheoMalop
	@Malop varchar(10)
	AS
		SELECT * FROM LICHHOC WHERE Malop = @Malop

	CREATE PROCEDURE DS_LichHoc
	AS
		SELECT * FROM LICHHOC 

	CREATE TABLE KETQUAHOCTAP
	(
	MaLKQ varchar(10),
	LoaiKQ nvarchar(15),
	CONSTRAINT PK_KQHT PRIMARY KEY (MaLKQ)
	)

	------------------------------------------------------------------------
	drop table HOCLUC
	CREATE TABLE HOCLUC
	(
	MaLKQ varchar(10),
	MaHS int,
	Thang int,
	Nam int,
	CONSTRAINT PK_HL PRIMARY KEY (MaLKQ, MaHS, Thang, Nam)
	)
	ALTER TABLE HOCLUC ADD CONSTRAINT FK1_HOCLUC FOREIGN KEY(MaHS) REFERENCES HOCSINH(MaHS)
	ALTER TABLE HOCLUC ADD CONSTRAINT FK2_HOCLUC FOREIGN KEY(MaLKQ) REFERENCES KETQUAHOCTAP(MaLKQ)


	create procedure DS_HocLucTheoMaHS
	@MaHS int
	as
		Select *from HOCLUC WHERE MaHS = @MaHS

	create procedure DS_HocLuc
	as
	Select *from HOCLUC

	----------------------------------------------------------------------------

	CREATE TABLE DANGKI
	(
	MaDV varchar(10),
	MaHS int,
	Tungay smalldatetime,
	Denngay smalldatetime
	CONSTRAINT PK_PC PRIMARY KEY (MaDV,MaHS)
	)

	ALTER TABLE DANGKI ADD CONSTRAINT FK1_DANGKI FOREIGN KEY(MaHS) REFERENCES HOCSINH(MaHS)
	ALTER TABLE DANGKI ADD CONSTRAINT FK2_DANGKI FOREIGN KEY(MaDV) REFERENCES DICHVU(MaDV)

	create procedure DS_DangKiTheoMaHS
	@MaHS int
	as
		Select *from DANGKI WHERE MaHS = @MaHS
	
	create procedure DS_DangKiTheoMaDV
	@MaDV varchar(10)
	as
		Select *from DANGKI WHERE MaDV = @MaDV

	create procedure DS_DangKiDV
	as
		Select *from DANGKI


	------------------------------------------------------------------------


	CREATE TABLE CHITIETHOADON
	(
	MaHP varchar(10),
	MaHD int
	CONSTRAINT PK_CTHD PRIMARY KEY (MaHP,MaHD)
	)
	create procedure DS_ChiTietHoaDon
	as
		select *from chitiethoadon

	-------------------------------------------------------

	CREATE TABLE HOADON
	(
	MaHD int identity (1,1),
	MaHS int,
	MaNV int,
	TenHD nvarchar(50),
	NgayHD smalldatetime,
	Trigia money,
	CONSTRAINT PK_HD PRIMARY KEY (MaHD)
	)
	use QLMN
	alter table HOADON alter column Trigia money
	create procedure DS_HoaDon
	as
		select *from HOADON

	create procedure DS_HoaDonTheoMaHS
	@MaHS int
	as
		Select *from HOADON WHERE MaHS = @MaHS
	-----------------------------------------------
	CREATE TABLE DICHVU
	(
	MaDV varchar(10),
	TenDV nvarchar(50),
	CONSTRAINT PK_DV PRIMARY KEY (MaDV)
	)

	INSERT INTO DICHVU VALUES ('GTNG',N'Giữ trẻ ngoài giờ')
	INSERT INTO DICHVU VALUES ('NKAV',N'Năng khiếu anh văn')
	INSERT INTO DICHVU VALUES ('NKTD',N'Năng khiếu thể dục')
	INSERT INTO DICHVU VALUES ('NKVE',N'Năng khiếu vẽ')

	create procedure DS_DichVu
	as
		select *from DICHVU

	use QLMN
	//Thống kê kêt quả học tập theo lớp
	drop procedure DS_KQHTcuaLopTheoThang
	create procedure DS_KQHTcuaLopTheoThang
	@Thang int,
	@Malop varchar(10)
	as
		Select DANHSACHLOP.MaHS, TenHS, LoaiKQ from KETQUAHOCTAP, DANHSACHLOP, HOCLUC, HOCSINH
		WHERE KETQUAHOCTAP.MaLKQ=HOCLUC.MaLKQ and HOCLUC.MaHS=DANHSACHLOP.MaHS and DANHSACHLOP.MaHS=HOCSINH.MaHS and Thang=@Thang and Malop=@Malop

	





